package com.css.fxfzypg.modules.auxiliary.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.auxiliary.entity.AuxiliaryMap;
import com.css.fxfzypg.modules.auxiliary.param.MapParam;
import com.css.fxfzypg.modules.auxiliary.param.QueryParam;
import com.css.fxfzypg.modules.auxiliary.repository.AuxiliaryMapRepository;
import com.css.fxfzypg.util.PlatformDateUtils;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class AuxiliaryMapRepositoryImpl implements AuxiliaryMapRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void save(AuxiliaryMap auxiliaryMap) {
        String sql = "INSERT INTO ypg_auxiliary_map(id, name, seismogenic_time, magnitude, longitude, latitude, upload_user, upload_time, del_flag, create_user, update_user, update_time, map_id, type,cata_id) values(:id, :name, :seismogenicTime, :magnitude, :longitude, :latitude, :uploadUser, :uploadTime, :delFlag, :createUser, :updateUser, :updateTime, :mapId, :type, :cataId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(auxiliaryMap);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Map<String, Object> getAll(QueryParam queryParam) {
        StringBuilder sql = new StringBuilder("select * from ypg_auxiliary_map");
        StringBuilder countSql = new StringBuilder("select count(*) from ypg_auxiliary_map");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        if (!PlatformObjectUtils.isEmpty(queryParam.getName())) {
            whereSql.append(" and name like '%" + queryParam.getName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParam.getUploadUser())) {
            whereSql.append(" and upload_user like '%" + queryParam.getUploadUser() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParam.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParam.getEndTime())) {
            whereSql.append(" and upload_time between '" + Timestamp.valueOf(queryParam.getStartTime()) + "' and '" + Timestamp.valueOf(queryParam.getEndTime()) + "'");
        }
        StringBuilder orderSql = new StringBuilder(" order by upload_time desc");
        int firstIndex = (queryParam.getPage() - 1) * queryParam.getRows();
        int lastIndex = queryParam.getRows();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql1 = countSql.append(whereSql);
        Integer total = jdbcTemplate.queryForObject(sql1.toString(), Integer.class);
        String sql2 = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        List<AuxiliaryMap> maps = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(AuxiliaryMap.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", maps);
        return map;
    }

    @Override
    public AuxiliaryMap getById(String id) {
        String sql = "select * from ypg_auxiliary_map where id = '" + id + "'";
        AuxiliaryMap auxiliaryMap = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(AuxiliaryMap.class));
        return auxiliaryMap;
    }

    @Override
    public AuxiliaryMap getByCataId(String cataId,String type) {
        AuxiliaryMap auxiliaryMap = null;
        String sql = "select * from ypg_auxiliary_map where cata_id = '" + cataId + "' and type='"+type+"' and del_flag='0'";
        List<AuxiliaryMap> auxiliaryMapList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AuxiliaryMap.class));
        if(auxiliaryMapList!=null && auxiliaryMapList.size()>0){
            auxiliaryMap = auxiliaryMapList.get(0);
        }
        return auxiliaryMap;
    }

    @Override
    public void deleteByIds(List<String> ids) {
        Timestamp currentTimestamp = PlatformDateUtils.getCurrentTimestamp();
        String userId = PlatformSessionUtils.getUserId();
        String sql = "update ypg_auxiliary_map set del_flag = '" + YNEnum.Y + "' , update_time = '" + currentTimestamp + "' ,update_user = '" + userId + "'" + " where id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);

    }

    @Override
    public void update(AuxiliaryMap auxiliaryMap) {
        String sql = "UPDATE ypg_auxiliary_map SET id=:id, name=:name, seismogenic_time=:seismogenicTime, magnitude=:magnitude, longitude=:longitude, latitude=:latitude, upload_user=:uploadUser, upload_time=:uploadTime, del_flag=:delFlag, create_user=:createUser, update_user=:updateUser, update_time=:updateTime, map_id=:mapId, type=:type WHERE id=:id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(auxiliaryMap);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public List<AuxiliaryMap> check(MapParam mapParam) {
        StringBuilder sql = new StringBuilder("select * from ypg_auxiliary_map where del_flag = '0'");
        if (!PlatformObjectUtils.isEmpty(mapParam.getId())) {
            sql.append(" and id != '" + mapParam.getId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapParam.getType())) {
            sql.append(" and type = '" + mapParam.getType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapParam.getSeismogenicTime())) {
            sql.append(" and seismogenic_time = '" + mapParam.getSeismogenicTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapParam.getMagnitude())) {
            sql.append(" and magnitude = '" + mapParam.getMagnitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapParam.getLongitude())) {
            sql.append(" and longitude = " + mapParam.getLongitude() + " ");
        }
        if (!PlatformObjectUtils.isEmpty(mapParam.getLatitude())) {
            sql.append(" and latitude = " + mapParam.getLatitude() + " ");
        }
        List<AuxiliaryMap> auxiliaryMaps = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(AuxiliaryMap.class));
        return auxiliaryMaps;
    }





    @Override
    public String getMapPictureByCataId(String cataId, String s) {
        String sql = null;
        try {
            sql = "SELECT (b.file_dir_id||b.attach_path ) FROM ypg_auxiliary_map a INNER JOIN sys_attachment_info b on a.map_id=b.attach_id where a.del_flag='0' and a.type='"+s+"'";
            return jdbcTemplate.queryForObject(sql,String.class);
        } catch (Exception e) {
            return null;
        }

    }

}
